package com.personnel.service.impl;

import com.personnel.mapper.StaffMapper;
import com.personnel.pojo.Staff;
import com.personnel.util.MD5Util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: YuePeng
 * @Date: 2022/2/17 10:06
 * @Version: 1.0
 */
@Service
public class ImportExcelServiceImpl {
    @Resource
    private StaffMapper staffMapper;
    @Transactional
    public Map<String, Object> importStaffExcel(MultipartFile file, HttpServletRequest request, String abPath) throws IOException {
        Map<String, Object> map = new HashMap<>();
        String uploadPath = "D:\\IdeaProject\\school\\personnel\\src\\main\\resources\\static\\upload\\"+abPath+"\\";
//        String uploadPath = "/home/springboot/upload/uploadExcel/";
        String fileName = file.getOriginalFilename();                   // 获取上传的文件名
        File newFile = new File(uploadPath + fileName);         // 根据当前工程的物理路径+文件名生成一个file对象
        System.out.println("newFile=" + newFile);
        FileCopyUtils.copy(file.getBytes(), newFile);                   // 通过上传通道中的字节码完成file文件的写入操作

        String path = newFile.toString();                                  // 获取文件的物理路径
        InputStream is = new FileInputStream(path);                       // 生成输入流
        HSSFWorkbook workbook = new HSSFWorkbook(is);                      // HSSFWorkbook类型对应2003版本
        Staff staff = null;
        Sheet sheet = workbook.getSheetAt(0);                       //模板数据默认读取第一个工作页sheet，index默认从0开始，如果存在多个sheet，那么需要循环Sheet判断


        //循环插入
        for(int i = 3; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            staff = new Staff();
            Cell cell = row.getCell(0);
            cell.setCellType(CellType.STRING);
            staff.setStaffName(cell.getStringCellValue());
            cell = row.getCell(1);
            cell.setCellType(CellType.STRING);
            staff.setSex(cell.getStringCellValue());
            cell = row.getCell(2);
            cell.setCellType(CellType.NUMERIC);
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            staff.setBirthday(Date.valueOf(df.format(cell.getDateCellValue())));
            cell = row.getCell(3);
            cell.setCellType(CellType.STRING);
            staff.setIdNumber(cell.getStringCellValue());
            cell = row.getCell(4);
            cell.setCellType(CellType.STRING);
            staff.setPoliticsStatus(cell.getStringCellValue());
            cell = row.getCell(5);
            cell.setCellType(CellType.STRING);
            staff.setMarriage(cell.getStringCellValue());
            cell = row.getCell(6);
            cell.setCellType(CellType.STRING);
            staff.setNativePlace(cell.getStringCellValue());
            cell = row.getCell(7);
            cell.setCellType(CellType.STRING);
            staff.setPhone(cell.getStringCellValue());
            cell = row.getCell(8);
            cell.setCellType(CellType.STRING);
            staff.setEmail(cell.getStringCellValue());
            cell = row.getCell(9);
            cell.setCellType(CellType.STRING);
            staff.setGraduateSchool(cell.getStringCellValue());
            cell = row.getCell(10);
            cell.setCellType(CellType.STRING);
            staff.setSpecialty(cell.getStringCellValue());
            cell = row.getCell(11);
            cell.setCellType(CellType.NUMERIC);
            staff.setGraduationDate(Date.valueOf(df.format(cell.getDateCellValue())));
            cell = row.getCell(12);
            cell.setCellType(CellType.NUMERIC);
            staff.setHiredate(Date.valueOf(df.format(cell.getDateCellValue())));
            cell = row.getCell(13);
            cell.setCellType(CellType.STRING);
            staff.setCategory(cell.getStringCellValue());
            cell = row.getCell(14);
            cell.setCellType(CellType.STRING);
            staff.setStaffDepartmentId(Integer.valueOf(cell.getStringCellValue()));
            cell = row.getCell(15);
            cell.setCellType(CellType.STRING);
            staff.setStaffJobId(Integer.valueOf(cell.getStringCellValue()));
            cell = row.getCell(16);
            cell.setCellType(CellType.STRING);
            staff.setDirectSuperior(cell.getStringCellValue());
            cell = row.getCell(17);
            cell.setCellType(CellType.STRING);
            staff.setBankAccount(cell.getStringCellValue());
            cell = row.getCell(18);
            cell.setCellType(CellType.STRING);
            staff.setLoginName(cell.getStringCellValue());
            cell = row.getCell(19);
            cell.setCellType(CellType.STRING);
            staff.setComment(cell.getStringCellValue());
            staff.setPassword(MD5Util.getMD5String("123456"));
            //System.out.println(staff);
            staffMapper.insert(staff);
        }
        map.put("code", 200);
        map.put("msg", "人员信息导入成功");
        return map;
    }
}
